package myta.bean;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.faces.component.html.HtmlInputText;

import myta.model.DataTable;
import myta.service.Command;
import myta.service.SQLexec;
import myta.service.Utility;

public class RapotSiswa {
	private HtmlInputText txtthnajaran;
	
	private DataTable datatable;
	
	public RapotSiswa() {
		try {
			txtthnajaran = new HtmlInputText();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			txtthnajaran.setValue(sdf.format(Calendar.getInstance().getTime()));
			
			sdf = new SimpleDateFormat("yyyy");
			loadList(sdf.format(Calendar.getInstance().getTime()), "");
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}
	
	private void loadList(String thnajaran, String idkelas) throws Exception{
//		listNis = new ArrayList<SelectItem>();
//		listKelas = new ArrayList<SelectItem>();
//		listMapel = new ArrayList<SelectItem>();
//		
//		SelectItem s = new SelectItem();
//		s.setValue("-");
//		s.setLabel("All");
//		
//		listNis.add(s);
//		listKelas.add(s);
//		listMapel.add(s);
//		
//		String sql = "select distinct sw.nis,sw.nama from jadwal j inner join mapel m on m.idmapel = j.idmapel inner join kelassiswa ks on j.idkelassiswa = ks.idkelassiswa inner join kelas k on ks.idkelas=k.idkelas inner join semester s on k.idsemester=s.idsemester inner join kelassiswa_detail ksd on ks.idkelassiswa = ksd.idkelassiswa inner join siswa sw on sw.nis = ksd.nis where s.thnajaran = ? and k.idkelas like ?";
//		Command command = SQLexec.makeCommand(sql);
//		command.setObject(1, Integer.valueOf(thnajaran));
//		command.setObject(2, "%"+idkelas+"%");
//		DataTable dt = command.executeQuery();
//		
//		for(int i=0;i<dt.getRowCount();i++){
//			DataRow dr = dt.getRow(i);
//			
//			s = new SelectItem();
//			s.setValue(dr.getString("nis"));
//			s.setLabel(dr.getString("nama"));
//			
//			listNis.add(s);
//		}
//		
//		sql = "select distinct m.idmapel,m.namamapel from jadwal j inner join mapel m on m.idmapel = j.idmapel inner join kelassiswa ks on j.idkelassiswa = ks.idkelassiswa inner join kelas k on ks.idkelas=k.idkelas inner join semester s on k.idsemester=s.idsemester where s.thnajaran = ? and k.idkelas like ?";
//		command = SQLexec.makeCommand(sql);
//		command.setObject(1, Integer.valueOf(thnajaran));
//		command.setObject(2, "%"+idkelas+"%");
//		dt = command.executeQuery();
//		
//		for(int i=0;i<dt.getRowCount();i++){
//			DataRow dr = dt.getRow(i);
//			
//			s = new SelectItem();
//			s.setValue(dr.getString("idmapel"));
//			s.setLabel(dr.getString("namamapel"));
//			
//			listMapel.add(s);
//		}
//		
//		sql = "select distinct k.idkelas,k.namakelas from jadwal j inner join mapel m on m.idmapel = j.idmapel inner join kelassiswa ks on j.idkelassiswa = ks.idkelassiswa inner join kelas k on ks.idkelas=k.idkelas inner join semester s on k.idsemester=s.idsemester where s.thnajaran = ? and k.idkelas like ?";
//		command = SQLexec.makeCommand(sql);
//		command.setObject(1, Integer.valueOf(thnajaran));
//		command.setObject(2, "%"+idkelas+"%");
//		dt = command.executeQuery();
//		
//		for(int i=0;i<dt.getRowCount();i++){
//			DataRow dr = dt.getRow(i);
//			
//			s = new SelectItem();
//			s.setValue(dr.getString("idkelas"));
//			s.setLabel(dr.getString("namakelas"));
//			
//			listKelas.add(s);
//		}
	}
	
	private void loadDatatable(String thnajaran, String nis, String idkelas, String idmapel) throws Exception{
		datatable = new DataTable();
		
		String sql = "select thnajaran, namakelas, nis, nama, namamapel, kd, uts, uas, cast((kd + uts + (2 * uas)) / 4  as signed) as na from ( ";
		sql += "select ";
		sql += "thnajaran, namakelas, nis, nama, namamapel, tugas, ulangan_harian, cast((tugas + ulangan_harian) / 2 as signed) as kd , uts, uas ";
		sql += "from ( ";
		
		sql += "select ";
		sql += "sm.thnajaran, k.namakelas, s.nis, s.nama, m.namamapel, sum(if(jn.jenisnilai='Tugas',nd.nilai,0)) as tugas, ";
		sql += "sum(if(jn.jenisnilai='Ulangan Harian',nd.nilai,0)) as ulangan_harian,  ";
		sql += "sum(if(jn.jenisnilai='UTS',nd.nilai,0)) as uts, sum(if(jn.jenisnilai='UAS',nd.nilai,0)) as uas ";
		sql += "from nilai n ";
		sql += "inner join nilai_detail nd on n.idnilai = nd.idnilai ";
		sql += "inner join jenisnilai jn on n.idjenisnilai = jn.idjenisnilai ";
		sql += "inner join mapel m on n.idmapel = m.idmapel ";
		sql += "inner join siswa s on s.nis = nd.nis ";
		sql += "inner join kelassiswa ks on n.idkelassiswa = ks.idkelassiswa ";
		sql += "inner join kelas k on ks.idkelas = k.idkelas ";
		sql += "inner join semester sm on k.idsemester = sm.idsemester ";
		sql += "where sm.thnajaran = ? and nd.nis like ? and ks.idkelas like ? and n.idmapel like ? ";
		sql += "group by sm.thnajaran, k.namakelas, s.nis, s.nama, m.namamapel ";
		sql += "order by sm.thnajaran, k.namakelas, s.nama, m.namamapel ";
		
//		sql += "select ";
//		sql += "sm.thnajaran, k.namakelas, s.nis, s.nama, m.namamapel, sum(if(jn.jenisnilai='Tugas',n.nilai,0)) as tugas, ";
//		sql += "sum(if(jn.jenisnilai='Ulangan Harian',n.nilai,0)) as ulangan_harian,  ";
//		sql += "sum(if(jn.jenisnilai='UTS',n.nilai,0)) as uts, sum(if(jn.jenisnilai='UAS',n.nilai,0)) as uas ";
//		sql += "from nilai n ";
//		sql += "inner join jenisnilai jn on n.idjenisnilai = jn.idjenisnilai ";
//		sql += "inner join mapel m on n.idmapel = m.idmapel ";
//		sql += "inner join siswa s on s.nis = n.nis ";
//		sql += "inner join kelassiswa ks on n.idkelassiswa = ks.idkelassiswa ";
//		sql += "inner join kelas k on ks.idkelas = k.idkelas ";
//		sql += "inner join semester sm on k.idsemester = sm.idsemester ";
//		sql += "where sm.thnajaran = ? and n.nis like ? and ks.idkelas like ? and n.idmapel like ? ";
//		sql += "group by sm.thnajaran, k.namakelas, s.nis, s.nama, m.namamapel ";
//		sql += "order by sm.thnajaran, k.namakelas, s.nama, m.namamapel ";
		
		sql += ") as datanilai ";
		sql += ") as datanilaisiswa;";
		
		Command command = SQLexec.makeCommand(sql);
		command.setObject(1, Integer.valueOf(thnajaran));
		command.setObject(2, nis.equalsIgnoreCase("-")?"%%":"%"+nis+"%");
		command.setObject(3, idkelas.equalsIgnoreCase("-")?"%%":"%"+idkelas+"%");
		command.setObject(4, idmapel.equalsIgnoreCase("-")?"%%":"%"+idmapel+"%");
		
		datatable = command.executeQuery();
	}
	
//	public void thnajaranonchange(){
//		try {
//			loadList(txtthnajaran.getValue().toString(), "");
//		} catch (Exception e) {
//			Utility.showMessage(e.getMessage());
//		}
//	}
	
//	public void idkelasonchange(){
//		try {
//			loadList(txtthnajaran.getValue().toString(), txtidkelas.getValue().toString());
//		} catch (Exception e) {
//			Utility.showMessage(e.getMessage());
//		}
//	}
	
	public void proses(){
		try {
			SessionSiswa s = (SessionSiswa)Utility.getBean("sessionSiswa");
//			loadDatatable(txtthnajaran.getValue().toString(), txtnis.getValue().toString(), txtidkelas.getValue().toString(), txtidmapel.getValue().toString());
			loadDatatable(txtthnajaran.getValue().toString(), s.getNis(), "", "");
		} catch (Exception e) {
			Utility.showMessage(e.getMessage());
		}
	}

	public HtmlInputText getTxtthnajaran() {
		return txtthnajaran;
	}

	public void setTxtthnajaran(HtmlInputText txtthnajaran) {
		this.txtthnajaran = txtthnajaran;
	}

//	public HtmlSelectOneMenu getTxtidkelas() {
//		return txtidkelas;
//	}
//
//	public void setTxtidkelas(HtmlSelectOneMenu txtidkelas) {
//		this.txtidkelas = txtidkelas;
//	}
//
//	public HtmlSelectOneMenu getTxtidmapel() {
//		return txtidmapel;
//	}
//
//	public void setTxtidmapel(HtmlSelectOneMenu txtidmapel) {
//		this.txtidmapel = txtidmapel;
//	}
//
//	public HtmlSelectOneMenu getTxtnis() {
//		return txtnis;
//	}
//
//	public void setTxtnis(HtmlSelectOneMenu txtnis) {
//		this.txtnis = txtnis;
//	}

	public DataTable getDatatable() {
		return datatable;
	}

	public void setDatatable(DataTable datatable) {
		this.datatable = datatable;
	}

//	public List<SelectItem> getListKelas() {
//		return listKelas;
//	}
//
//
//
//	public void setListKelas(List<SelectItem> listKelas) {
//		this.listKelas = listKelas;
//	}
//
//
//
//	public List<SelectItem> getListMapel() {
//		return listMapel;
//	}
//
//
//
//	public void setListMapel(List<SelectItem> listMapel) {
//		this.listMapel = listMapel;
//	}
//
//
//
//	public List<SelectItem> getListNis() {
//		return listNis;
//	}
//
//
//
//	public void setListNis(List<SelectItem> listNis) {
//		this.listNis = listNis;
//	}
}
